﻿/*
 * 文件名:Teaching.cs
 * 类名:XHCTMS.DAL.Teaching
 * 创建人: 杨航
 * 创建时间: 2013-5-21 10:04:43
 * 
 * 最近更新：2013-5-23 15:43:58
 * 更新内容：完成分页查询
 * 
 * 备注: 
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    /// <summary>
    /// 授课任务
    /// </summary>
    public class Teaching
    {
        /*存储过程
           ----查询所有数据----
            DROP VIEW V_SEARCH
            CREATE VIEW V_SEARCH
            AS
            select A.teaching_id, B.[user_name], A.teaching_lesson, C.class_name, A.teaching_state, A.teaching_class_Id, A.teaching_teacher_Id
            from (teaching A left join [user] B
            on A.teaching_teacher_Id=B.[user_id]) left join class C on A.teaching_class_Id=C.class_id

            SELECT * FROM V_SEARCH
       
            ----多条件查询----
            CREATE PROC P_SEARCHINFO
            @TEACHINGID INT=NULL,
            @TEACHINGCLASSID INT=NULL,
            @TEACHERID INT=NULL,
            @TEACHINGLESSON NVARCHAR=NULL,
            @TEACHINGSTATE INT=NULL
            AS
            BEGIN
            SET NOCOUNT ON
            SELECT * FROM V_SEARCH
            WHERE (@TEACHINGID IS NULL OR TEACHING_ID=@TEACHINGID)
	            AND	(@TEACHINGCLASSID IS NULL OR TEACHING_CLASS_ID=@TEACHINGCLASSID)
                AND	(@TEACHERID IS NULL OR TEACHING_TEACHER_ID=@TEACHERID)
                AND	(@TEACHINGLESSON IS NULL OR TEACHING_LESSON=@TEACHINGLESSON)
                AND	(@TEACHINGSTATE IS NULL OR TEACHING_STATE=@TEACHINGSTATE)
            END

            EXEC P_SEARCHINFO NULL,NULL,NULL,NULL,1
         
           ----分页查询----
            CREATE PROCEDURE SP_PAGINATION
            @PAGESIZE INT,					--页面的大小
            @PAGEINDEX INT					--当然的页号
            AS
            BEGIN
             WITH TEMP AS (
            SELECT ROW_NUMBER() OVER (ORDER BY teaching_id DESC)AS ROW, * FROM v_search)

             SELECT * FROM TEMP WHERE ROW BETWEEN 

            (@PAGEINDEX-1)*@PAGESIZE+1 AND (@PAGEINDEX-1)*@PAGESIZE+@PAGESIZE

            END

            EXEC SP_PAGINATION 2, 1
         */


        /// <summary>
        /// 添加授课任务
        /// </summary>
        /// <param name="teaching">授课任务信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Add(MODEL.Teaching teaching)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("insert into teaching values(@TeachingClassId, @TeachingTeacherId, @TeachingLesson, default, null, '在授');");
            SqlParameter[] paras = { 
                                       new SqlParameter("@TeachingClassId", SqlDbType.Int),
                                       new SqlParameter("@TeachingTeacherId", SqlDbType.Int),
                                       new SqlParameter("@TeachingLesson", SqlDbType.NVarChar, 64),                                       
                                   };

            paras[0].Value = teaching.TeachingClassId;
            paras[1].Value = teaching.TeachingTeachId;
            paras[2].Value = teaching.TeachingLesson;
            
            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 修改授课任务状态 1/在授, 0/结课
        /// </summary>
        /// <param name="teachingId">授课任务编号</param>
        /// <param name="IsEndOrOn">授课任务状态 1/在授, 0/结课</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int ReviseFlag(int teachingId, int IsEndOrOn)
        {
            StringBuilder queryString = new StringBuilder();
            if (IsEndOrOn == 0)
            {
                queryString.Append(@"update teaching
                                    set teaching_state=@IsEndOrOn, teaching_strstate='结课未打分', teaching_time=GETDATE()
                                    where teaching_id=@TeachingId");
            }
            if (IsEndOrOn == -1)
            {
                queryString.Append(@"update teaching
                                    set teaching_state=@IsEndOrOn, teaching_strstate='已打分'
                                    where teaching_id=@TeachingId");
            }
            if (IsEndOrOn == -2)
            {
                queryString.Append(@"update teaching
                                    set teaching_state=@IsEndOrOn, teaching_strstate='删除'
                                    where teaching_id=@TeachingId");
            }
            
            SqlParameter[] paras = {
                                       new SqlParameter("@IsEndOrOn", SqlDbType.Int),
                                       new SqlParameter("@TeachingId", SqlDbType.Int)
                                   };
            paras[0].Value = IsEndOrOn;
            paras[1].Value = teachingId;
            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 通过编号删除相关授课任务
        /// </summary>
        /// <param name="teachingId">授课任务编号</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Delete(int teachingId)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("delete from teaching where teaching_id=@TeachingId");
            SqlParameter[] paras = {
                                       new SqlParameter("@TeachingId", SqlDbType.Int)
                                   };
            paras[0].Value = teachingId;
            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 分页获取授课信息
        /// </summary>
        /// <param name="pageSize">页面大小</param>
        /// <param name="pageIndex">当前页号</param>
        /// <returns>返回DataTable null/异常</returns>
        public DataTable Pagination(int pageSize, int pageIndex)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("sp_Pagination");
            SqlParameter[] paras = {
                                       new SqlParameter("@PageSize", SqlDbType.Int),
                                       new SqlParameter("@PageIndex", SqlDbType.Int)
                                   };
            paras[0].Value = pageSize;
            paras[1].Value = pageIndex;
            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        /// <summary>
        /// 多条件获取授课任务信息
        /// </summary>
        /// <param name="teachingId">授课任务编号</param>
        /// <returns>返回DataTable类型，null/异常</returns>
        public DataTable Search(MODEL.Teaching teaching, int pageSize, int pageIndex)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("p_SearchInfo");
            SqlParameter[] paras = {
                                       new SqlParameter("@TeachingId", SqlDbType.Int),
                                       new SqlParameter("@TeachingClassId", SqlDbType.Int),
                                       new SqlParameter("@TeacherId", SqlDbType.Int),
                                       new SqlParameter("@TeachingLesson", SqlDbType.NVarChar, 64),
                                       new SqlParameter("@TeachingState", SqlDbType.Int),
                                       new SqlParameter("@PageSize", SqlDbType.Int),
                                       new SqlParameter("@PageIndex", SqlDbType.Int)
                                   };
            if (teaching.TeachingId != null)
            {
                paras[0].Value = teaching.TeachingId;
            }
            else
            {
                paras[0].Value = null;
            }
            if (teaching.TeachingClassId != null)
            {
                paras[1].Value = teaching.TeachingClassId;
            }
            else
            {
                paras[1].Value = null;
            }
            if (teaching.TeachingTeachId != null)
            {
                paras[2].Value = teaching.TeachingTeachId;
            }
            else
            {
                paras[2].Value = null;
            }
            if (teaching.TeachingLesson != null)
            {
                paras[3].Value = teaching.TeachingLesson;
            }
            else
            {
                paras[3].Value = null;
            }
            if (teaching.TeachingState != null)
            {
                paras[4].Value = teaching.TeachingState;  
            }
            else
            {
                paras[4].Value = null;
            }
            paras[5].Value = pageSize;
            paras[6].Value = pageIndex;                      
            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        /// <summary>
        /// 通过编号修改授课任务信息
        /// </summary>
        /// <param name="teachingId">授课任务编号</param>
        /// <param name="teaching">授课任务信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Revise(int teachingId, MODEL.Teaching teaching)
        {
            StringBuilder queryString = new StringBuilder();
            switch (teaching.TeachingState)
	        {
                case 1:
                    queryString.Append(@"update teaching 
                                    set  teaching_class_Id=@TeachingClassId, teaching_teacher_Id=@TeachingTeachId,
                                    teaching_lesson=@TeachingLesson, teaching_state=@TeachingState, teaching_strstate='在授'  
                                    where teaching_id = @TeachingId");
                    break;
                case 0:
                    queryString.Append(@"update teaching 
                                    set  teaching_class_Id=@TeachingClassId, teaching_teacher_Id=@TeachingTeachId,
                                    teaching_lesson=@TeachingLesson, teaching_state=@TeachingState, teaching_strstate='结课未打分'  
                                    where teaching_id = @TeachingId");
                    break;
                case -1:
                    queryString.Append(@"update teaching 
                                    set  teaching_class_Id=@TeachingClassId, teaching_teacher_Id=@TeachingTeachId,
                                    teaching_lesson=@TeachingLesson, teaching_state=@TeachingState, teaching_strstate='已打分'  
                                    where teaching_id = @TeachingId");
                    break;
                case -2:

		        
                    queryString.Append(@"update teaching 
                                    set  teaching_class_Id=@TeachingClassId, teaching_teacher_Id=@TeachingTeachId,
                                    teaching_lesson=@TeachingLesson, teaching_state=@TeachingState, teaching_strstate='删除'  
                                    where teaching_id = @TeachingId");
                    break;

                default:
                    break;
	        }    
            SqlParameter[] paras = {
                                       new SqlParameter("@TeachingId", SqlDbType.Int),
                                       new SqlParameter("@TeachingClassId", SqlDbType.Int),
                                       new SqlParameter("@TeachingTeachId", SqlDbType.Int),
                                       new SqlParameter("@TeachingLesson", SqlDbType.NVarChar, 64),
                                       new SqlParameter("@TeachingState", SqlDbType.Int)
                                   };
            paras[0].Value = teachingId;
            paras[1].Value = teaching.TeachingClassId;
            paras[2].Value = teaching.TeachingTeachId;
            paras[3].Value = teaching.TeachingLesson;
            paras[4].Value = teaching.TeachingState;
            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 获取所有班级名
        /// </summary>
        /// <returns>返回DataTable类型，null/异常</returns>
        public DataTable GetClassName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select class_id,class_name from class order by class_name");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        /// <summary>
        /// 获取所有教师名
        /// </summary>
        /// <returns>返回DataTable类型，null/异常</returns>
        public DataTable GetTeacherName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select [USER_ID],[user_name] from [user]  order by [user_name]");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        public string GetCountPage(int pageSize)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select case  when COUNT(*)%@pageSize=0 then COUNT(*)/@pageSize else COUNT(*)/@pageSize+1 end from V_SEARCH");
            SqlParameter[] paras = {
                                       new SqlParameter("@pageSize", SqlDbType.Int)
                                   };
            paras[0].Value = pageSize;
            return new DBHELP.Common().GetDataScalar(queryString.ToString(), paras);
        }
        public DataTable GetCountPage(MODEL.Teaching teaching)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"P_GetPageCount");
            SqlParameter[] paras = {
                                        new SqlParameter("@TEACHINGID", SqlDbType.Int),
                                        new SqlParameter("@TEACHINGCLASSID", SqlDbType.Int),
                                        new SqlParameter("@TEACHERID", SqlDbType.Int),
                                        new SqlParameter("@TEACHINGLESSON", SqlDbType.NVarChar, 64),
                                        new SqlParameter("@TEACHINGSTATE", SqlDbType.Int)
                                   };

            paras[0].Value = teaching.TeachingId;
            paras[1].Value = teaching.TeachingClassId;
            paras[2].Value = teaching.TeachingTeachId;
            paras[3].Value = teaching.TeachingLesson;
            paras[4].Value = teaching.TeachingState;

            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        /// <summary>
        /// 根据授课老师号查询正在授课的所有班级
        /// </summary>
        /// <param name="teaTeaID"></param>
        /// <returns></returns>
        public DataTable TeaTeaIDFindClass(int teaTeaID )
        {
            string sqlStr =@"select distinct tcl.class_id,tcl.class_name from teaching tte left join class tcl on tte.teaching_class_Id = tcl.class_id
  where  (tte.teaching_state =1 or tte.teaching_state =0) and  teaching_teacher_Id=@teaTeaID";

            SqlParameter[] paras = { new SqlParameter("@teaTeaID",teaTeaID),                                   
                                   };
            return new DBHELP.Common().GetDataTable(sqlStr, paras);
  
        }


        /// <summary>
        /// 授课老师获得所教班的在授课程
        /// </summary>
        /// <param name="classID"></param>
        /// <param name="teaTeaID"></param>
        /// <returns></returns>
        public DataTable GetTeaching(int classID , int teaTeaID ,bool isEnd)
        {
            string sqlStr = @"select tte.teaching_id,tte.teaching_lesson from teaching tte  left join [user] tu on tte.teaching_teacher_Id = tu.[user_id]
left join class tcl on tcl.class_id = tte.teaching_class_Id where tte.teaching_state =@teaState and tcl.class_id =@classID and tu.[user_id] = @teaTeaID";

            int state;
            if (isEnd)
            {
                state = 0;
            }
            else
            {
                state = 1;
            }
            SqlParameter[] paras = { new SqlParameter("@classID",classID),
                                       new SqlParameter("@teaTeaID", teaTeaID),
                                   new SqlParameter("@teaState",state)};
            return new DBHELP.Common().GetDataTable(sqlStr, paras);
        }



       

    }
}
